Filtering in JOIN

You can use where, and in a join to filter your table. If you want your table to return rows that have specific values, you can use where, and and.

where

Example:

We are using the countries table and regions for this example.

Let's join these two tables, but only include rows whose region_id is 2.

select * from countries left join regions on countries.region_id = regions.region_id where countries.region_id = 2;

Result:

The where condition applies to the table before the join. If you want the either of the table to return every row and the other to return only rows that satisfy the condition, use AND.

and

In AND, the table will return first, and then the condition in AND will be applied.

For Example:

select * from countries left join regions on countries.region_id = regions.region_id and countries.region_id = 2;

Result:

If you want every row from regions but only rows from countries that satisfy the condition, you have to use right join.

Ecto query to filter in join

where/3

Expression

HR.Country
|> join(:left, [p], r in HR.Region, on: p.region_id==r.region_id )
|> where([p], p.region_id==2 )
|> select([p, r], [p ,r])
|> HR.Repo.all()
  • join(:left, [p], r in HR.Region, on: p.region_id == r.region_id) -> Joining rows whose region_id is the same. Here p and r are reference variables. Refer to Aliases in Ecto to understand reference variables.
  • where([p], p.region_id == 2) -> Only include rows whose region_id is 2. You can also use other comparison operators here, such as !=, <, >.

Result:

iex(2)> HR.Country|> join(:left, [p], r in HR.Region, on: p.region_id==r.region_id ) |> where([p], p.region_id==2 ) |> select([p, r], [p ,r] ) |>  HR.Repo.all()

[
  [
    %HR.Country{
      __meta__: #Ecto.Schema.Metadata<:loaded, "countries">,
      country_id: "AR",
      country_name: "Argentina",
      region_id: 2
    },
    %HR.Region{
      __meta__: #Ecto.Schema.Metadata<:loaded, "regions">,
      region_id: 2,
      region_name: "Americas"
    }
  ],
  [
    %HR.Country{
      __meta__: #Ecto.Schema.Metadata<:loaded, "countries">,
      country_id: "BR",
      country_name: "Brazil",
      region_id: 2
    },
    %HR.Region{
      __meta__: #Ecto.Schema.Metadata<:loaded, "regions">,
      region_id: 2,
      region_name: "Americas"
    }
  ],
  [
    %HR.Country{
      __meta__: #Ecto.Schema.Metadata<:loaded, "countries">,
      country_id: "CA",
      country_name: "Canada",
      region_id: 2
    },
    %HR.Region{
      __meta__: #Ecto.Schema.Metadata<:loaded, "regions">,
      region_id: 2,
      region_name: "Americas"
    }
  ],
  [
    %HR.Country{
      __meta__: #Ecto.Schema.Metadata<:loaded, "countries">,
      country_id: "MX",
      country_name: "Mexico",
      region_id: 2
    },
    %HR.Region{
      __meta__: #Ecto.Schema.Metadata<:loaded, "regions">,
      region_id: 2,
      region_name: "Americas"
    }
  ],
  [
    %HR.Country{
      __meta__: #Ecto.Schema.Metadata<:loaded, "countries">,
      country_id: "US",
      country_name: "United States of America",
      region_id: 2
    },
    %HR.Region{
      __meta__: #Ecto.Schema.Metadata<:loaded, "regions">,
      region_id: 2,
      region_name: "Americas"
    }
  ]
]

and/2

Example:

HR.Country
|> join(:left, [p], r in HR.Region, on: p.region_id==r.region_id and p.region_id==2 )
|> select([p, r], [p ,r] )
|>  HR.Repo.all()

The above query will return every record from HR.Country, but only those that satisfy the AND condition.

Result:

iex(3)> HR.Country|> join(:left, [p], r in HR.Region, on: p.region_id==r.region_id and p.region_id==2 ) |> select([p, r], [p ,r] ) |>  HR.Repo.all()

[
  [
    %HR.Country{
      __meta__: #Ecto.Schema.Metadata<:loaded, "countries">,
      country_id: "AR",
      country_name: "Argentina",
      region_id: 2
    },
    %HR.Region{
      __meta__: #Ecto.Schema.Metadata<:loaded, "regions">,
      region_id: 2,
      region_name: "Americas"
    }
  ],
  [
    %HR.Country{
      __meta__: #Ecto.Schema.Metadata<:loaded, "countries">,
      country_id: "AU",
      country_name: "Australia",
      region_id: 3
    },
    nil
  ]
  [
    %HR.Country{
      __meta__: #Ecto.Schema.Metadata<:loaded, "countries">,
      country_id: "CH",
      country_name: "Switzerland",
      region_id: 1
    },
    nil
  ],
  [
    %HR.Country{
      __meta__: #Ecto.Schema.Metadata<:loaded, "countries">,
      country_id: "CN",
      country_name: "China",
      region_id: 3
    },
    nil
  ],
  [
    %HR.Country{
      __meta__: #Ecto.Schema.Metadata<:loaded, "countries">,
      country_id: "DE",
      country_name: "Germany",
      region_id: 1
    },
    nil
  ],
  [
    %HR.Country{
      __meta__: #Ecto.Schema.Metadata<:loaded, "countries">,
      country_id: "DK",
      country_name: "Denmark",
      region_id: 1
    },
    nil
  ],
  [
    %HR.Country{
      __meta__: #Ecto.Schema.Metadata<:loaded, "countries">,
      country_id: "EG",
      country_name: "Egypt",
      region_id: 4
    },
    nil
  ],
    [
    %HR.Country{
      __meta__: #Ecto.Schema.Metadata<:loaded, "countries">,
      country_id: "MX",
      country_name: "Mexico",
      region_id: 2
    },
    %HR.Region{
      __meta__: #Ecto.Schema.Metadata<:loaded, "regions">,
      region_id: 2,
      region_name: "Americas"
    }
  ],
  [
    %HR.Country{
      __meta__: #Ecto.Schema.Metadata<:loaded, "countries">,
      country_id: "SG",
      country_name: "Singapore",
      region_id: 3
    },
    nil
  ]
]
Back to Homepage